﻿using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
namespace XiaoSe.Data.SQLClient
{
    /// <summary>
    /// SQL帮助类
    /// </summary>
    public class SqlHelper
    {
        private SqlConnection _connect;
        private SqlCommand _command;
        private SqlDataAdapter _adapter;
        private SqlDataReader _reader;
        private string _connectstr;
        public string ConnectStr
        {
            get { return _connectstr; }
            set { _connectstr = value; }
        }
        public SqlHelper()
        {
        }
        public SqlHelper(string sqlConnectionString)
        {
            _connectstr = sqlConnectionString;
        }
        public object AddRecord<T>(T model, string removeField)
        {
            object obj;
            var mh = new ModelHelper();
            var commandText = "";
            var spList = mh.ModelToSqlParameter(model, CreateSqlText.CreateInsert, removeField.ToLower(), ref commandText, null);
            using (_connect = new SqlConnection(_connectstr))
            {
                _connect.Open();
                using (_command = new SqlCommand())
                {
                    _command.Connection = _connect;
                    _command.CommandText = commandText;
                    _command.Parameters.AddRange(spList);
                    obj = _command.ExecuteScalar();
                }
            }
            return obj;
        }
        public int UpdateRecord<T>(T model, string @where, string removeField)
        {
            var mh = new ModelHelper();
            var commandText = "";
            var spList = mh.ModelToSqlParameter(model, CreateSqlText.CreateUpdate, removeField.ToLower(), ref commandText, null);
            if (!string.IsNullOrEmpty(@where))
            {
                commandText += " where " + @where;
            }
            int affectrow;
            using (_connect = new SqlConnection(_connectstr))
            {
                _connect.Open();
                using (_command = new SqlCommand())
                {
                    _command.Connection = _connect;
                    _command.CommandText = commandText;
                    _command.Parameters.AddRange(spList);
                    affectrow = _command.ExecuteNonQuery();
                }
            }
            return affectrow;
        }
        public T SelectRecord<T>(string @where) where T : new()
        {
            string sComandText = "select top 1 * from " + typeof(T).Name;
            if (!string.IsNullOrEmpty(@where))
            {
                sComandText += " where " + @where;
            }
            using (_connect = new SqlConnection(_connectstr))
            {
                _connect.Open();
                using (_command = new SqlCommand())
                {
                    _command.Connection = _connect;
                    _command.CommandText = sComandText;
                    _reader = _command.ExecuteReader();
                    var list = DataReaderToList<T>(_reader);
                    _reader.Dispose();
                    _reader.Close();
                    return list.Count == 0 ? default(T) : list[0];
                }
            }
        }
        public List<T> SelectRecordList<T>(string @where) where T : new()
        {
            string sComandText = "select * from " + typeof(T).Name;
            if (!string.IsNullOrEmpty(@where))
            {
                sComandText += " where " + @where;
            }
            using (_connect = new SqlConnection(_connectstr))
            {
                _connect.Open();
                using (_command = new SqlCommand())
                {
                    _command.Connection = _connect;
                    _command.CommandText = sComandText;
                    _reader = _command.ExecuteReader();
                    var list = DataReaderToList<T>(_reader);
                    _reader.Dispose();
                    _reader.Close();
                    return list;
                }
            }
        }
        public int DeleteRecord(string tableName, string @where)
        {
            string sComandText = "delete from " + tableName;
            if (!string.IsNullOrEmpty(@where))
            {
                sComandText += " where " + @where;
            }
            int affectrow;
            using (_connect = new SqlConnection(_connectstr))
            {
                _connect.Open();
                using (_command = new SqlCommand())
                {
                    _command.Connection = _connect;
                    _command.CommandText = sComandText;
                    affectrow = _command.ExecuteNonQuery();
                }
            }
            return affectrow;
        }
        public DataSet ExecuteProc(string procName, SqlParameter[] paramList)
        {
            using (_connect = new SqlConnection(_connectstr))
            {
                var dataset = new DataSet();
                _connect.Open();
                using (_command = new SqlCommand())
                {
                    _command.Connection = _connect;
                    _command.CommandType = CommandType.StoredProcedure;
                    _command.CommandText = procName;
                    if (paramList != null && paramList.Length > 0)
                    {
                        _command.Parameters.AddRange(paramList);
                    }
                    using (_adapter = new SqlDataAdapter())
                    {
                        _adapter.SelectCommand = _command;
                        _adapter.Fill(dataset, "Test");
                        return dataset;
                    }
                }
            }
        }
        public object ExecuteProcEx(string procName, SqlParameter[] paramList)
        {
            using (_connect = new SqlConnection(_connectstr))
            {
                _connect.Open();
                using (_command = new SqlCommand())
                {
                    _command.Connection = _connect;
                    _command.CommandType = CommandType.StoredProcedure;
                    _command.CommandText = procName;
                    if (paramList != null && paramList.Length > 0)
                    {
                        _command.Parameters.AddRange(paramList);
                    }
                    return _command.ExecuteScalar();
                }
            }
        }
        public DataSet ExecuteSqlReturnDataSet(string sql, SqlParameter[] paramList)
        {
            using (_connect = new SqlConnection(_connectstr))
            {
                var dataset = new DataSet();
                _connect.Open();
                using (_command = new SqlCommand())
                {
                    _command.Connection = _connect;
                    _command.CommandType = CommandType.Text;
                    _command.CommandText = sql;
                    if (paramList != null && paramList.Length > 0)
                    {
                        _command.Parameters.AddRange(paramList);
                    }
                    using (_adapter = new SqlDataAdapter())
                    {
                        _adapter.SelectCommand = _command;
                        _adapter.Fill(dataset, "Test");
                        return dataset;
                    }
                }
            }
        }
        public object ExecuteSqlReturnScalar(string sql, SqlParameter[] paramList)
        {
            using (_connect = new SqlConnection(_connectstr))
            {
                _connect.Open();
                using (_command = new SqlCommand())
                {
                    _command.Connection = _connect;
                    _command.CommandType = CommandType.Text;
                    _command.CommandText = sql;
                    if (paramList != null && paramList.Length > 0)
                    {
                        _command.Parameters.AddRange(paramList);
                    }
                    return _command.ExecuteScalar();
                }
            }
        }
        public int ExecuteSqlReturnNonQuery(string sql, SqlParameter[] paramList)
        {
            using (_connect = new SqlConnection(_connectstr))
            {
                _connect.Open();
                using (_command = new SqlCommand())
                {
                    _command.Connection = _connect;
                    _command.CommandType = CommandType.Text;
                    _command.CommandText = sql;
                    if (paramList != null && paramList.Length > 0)
                    {
                        _command.Parameters.AddRange(paramList);
                    }
                    return _command.ExecuteNonQuery();
                }
            }
        }
        public SqlDataReader ExecuteReturnReader(string sql, SqlParameter[] paramList)
        {
            using (_connect = new SqlConnection(_connectstr))
            {
                _connect.Open();
                using (_command = new SqlCommand())
                {
                    _command.Connection = _connect;
                    _command.CommandType = CommandType.Text;
                    _command.CommandText = sql;
                    if (paramList != null && paramList.Length > 0)
                    {
                        _command.Parameters.AddRange(paramList);
                    }
                    return _command.ExecuteReader();
                }
            }
        }
        public DataSet SelectPageListByChildSelect(string tableName, string displayField, int pageCout, int pageIndex, string @where, string orderBy)
        {
            using (_connect = new SqlConnection(_connectstr))
            {
                var dataset = new DataSet();
                _connect.Open();
                using (_command = new SqlCommand())
                {
                    _command.Connection = _connect;
                    _command.CommandType = CommandType.Text;
                    var sqlCommand = "select Top " + pageCout + " " + displayField + " from " + tableName + " where " + @where + " and ID not in (";
                    sqlCommand += "select Top " + ((pageIndex - 1) * pageCout) + " ID from " + tableName + " where " + @where + " " + orderBy + ")" + orderBy;
                    _command.CommandText = sqlCommand;
                    using (_adapter = new SqlDataAdapter())
                    {
                        _adapter.SelectCommand = _command;
                        _adapter.Fill(dataset, "Test");
                        return dataset;
                    }
                }
            }
        }
        public int SelectCount(string tableName, string @where)
        {
            using (_connect = new SqlConnection(_connectstr))
            {
                _connect.Open();
                using (_command = new SqlCommand())
                {
                    _command.Connection = _connect;
                    _command.CommandType = CommandType.Text;
                    var sqlCommand = "select count(*) from " + tableName + " where " + @where;
                    _command.CommandText = sqlCommand;
                    return int.Parse(_command.ExecuteScalar().ToString());
                }
            }
        }
        public bool ExecuteSqlByTran(string[] sqlList)
        {
            using (_connect = new SqlConnection(_connectstr))
            {
                _connect.Open();
                SqlTransaction tran = _connect.BeginTransaction();
                using (_command = new SqlCommand())
                {
                    _command.Connection = _connect;
                    _command.Transaction = tran;
                    try
                    {
                        foreach (string sql in sqlList)
                        {
                            _command.CommandText = sql;
                            _command.ExecuteNonQuery();
                        }
                        tran.Commit();
                        tran.Dispose();
                        return true;
                    }
                    catch
                    {
                        tran.Rollback();
                        tran.Dispose();
                        return false;
                    }
                }
            }
        }
        public bool ExecuteSqlByTran(List<SqlTranInfo> sqlAndParame)
        {
            using (_connect = new SqlConnection(_connectstr))
            {
                _connect.Open();
                SqlTransaction tran = _connect.BeginTransaction();
                using (_command = new SqlCommand())
                {
                    _command.Connection = _connect;
                    _command.Transaction = tran;
                    try
                    {
                        foreach (SqlTranInfo info in sqlAndParame)
                        {
                            _command.CommandText = info.Sql;
                            _command.Parameters.Clear();
                            if (info.Parameter != null && info.Parameter.Length > 0)
                            {
                                _command.Parameters.AddRange(info.Parameter);
                            }
                            _command.ExecuteNonQuery();
                        }
                        tran.Commit();
                        tran.Dispose();
                        return true;
                    }
                    catch
                    {
                        tran.Rollback();
                        tran.Dispose();
                        return false;
                    }
                }
            }
        }
        public bool ExecuteSqlByTran(List<SqlTranInfo> sqlAndParame, ExecuteItem ei, ref object result)
        {
            using (_connect = new SqlConnection(_connectstr))
            {
                _connect.Open();
                SqlTransaction tran = _connect.BeginTransaction();
                using (_command = new SqlCommand())
                {
                    _command.Connection = _connect;
                    _command.Transaction = tran;
                    try
                    {
                        foreach (SqlTranInfo info in sqlAndParame)
                        {
                            _command.CommandText = info.Sql;
                            _command.Parameters.Clear();
                            if (info.Parameter != null && info.Parameter.Length > 0)
                            {
                                _command.Parameters.AddRange(info.Parameter);
                            }
                            if (ei == ExecuteItem.ExecuteNonQuery)
                            {
                                result = _command.ExecuteNonQuery();
                            }
                            if (ei == ExecuteItem.ExecuteScalar)
                            {
                                result = _command.ExecuteScalar();
                            }
                        }
                        tran.Commit();
                        tran.Dispose();
                        return true;
                    }
                    catch
                    {
                        tran.Rollback();
                        tran.Dispose();
                        return false;
                    }
                }
            }
        }
        public List<T> DataReaderToList<T>(DbDataReader reader) where T : new()
        {
            var mod = new ModelHelper();
            return mod.DataReaderToList<T>(reader);
        }
        public List<T> DataTableToList<T>(DataTable table) where T : new()
        {
            var mod = new ModelHelper();
            return mod.DataTableToList<T>(table);
        }
    }
    public class SqlTranInfo
    {

        public string Sql { get; set; }
        public SqlParameter[] Parameter;
    }
    public enum ExecuteItem
    {
        ExecuteNonQuery = 1,
        ExecuteScalar = 2
    }
}
